package com.esdk.sql;

import com.alibaba.fastjson.JSONArray;
import com.esdk.esdk;
import com.esdk.sql.orm.*;
import com.esdk.utils.*;

import java.sql.*;
import java.util.*;
import java.util.Date;

/***
 * @author 范飞宇
 * @since 2006.?.?
 */
public class JDBCTemplate implements ISQL, ISelect, IShowSql{
  private String _sql;
  private Connection _conn;
  private ResultSet _rs;
  private Object[] _parameters = new Object[0];
  private String querySQL;
  private int updatedCount;
  private boolean _noParameters = false;
  private Number generatedKey;
  private boolean isAutoIncrement;
  private int useCacheSec;
  private boolean isCachable = esdk.prop.getBoolean("db_cachable", false);
  private boolean isShowSql= SQLAssistant.IsShowSql;
  private int _limit = 0, _offset = 0;
  private SqlDialect sqlDialect=SQLAssistant.defaultDialect;
  private long executeConcumingTime;
  private boolean isShowFullSelectSql;

  private JDBCTemplate(String sql, Object[] parameters, Connection con) {
    _sql = sql;
    setConnection(con);
    _parameters = parameters;
  }

  public JDBCTemplate(Connection conn, String sql) {
    setConnection(conn);
    setSql(sql);
  }

  public JDBCTemplate(Connection conn, boolean noParams, String sql) {
    setConnection(conn);
    this._noParameters = noParams;
    setSql(sql);
  }

  /**
   * 是否需要打印sql
   */
  @Override public JDBCTemplate showSql(boolean isShowSql) {
    this.isShowSql=isShowSql;
    return this;
  }

  @Override public boolean showSql(){
    return this.isShowSql;
  }

  /**
   * 参数能正确处理in条件，Object可以为数组、Collection和Literal对象，eg.new Object[new Integer[],int,String]
   */
  public JDBCTemplate(Connection conn,String sql,Object...params){
    this._sql=sql;
    setConnection(conn);
    if(params.length>0 && sql.matches(".*?\\{\\d?\\}.*?")){
      for(int i=0;i<params.length;i++){
        params[i]=sqlDialect.getJDBCTemplateParams(params[i]);
      }
      setSql(esdk.str.format(sql,params));
    }else{
      this._parameters=params;
    }
  }

  /**
   * 参数能正确处理in条件，Object可以为数组、Collection和Literal对象，eg.new Object[new Integer[],int,String]
   */
  public JDBCTemplate(String sql, Object... parameters) {
    this(null,sql, parameters);
  }

  /**
   * 不用加单引号，参数命名方式：fieldname={key} and create_time={date} and center_id
   * in({ids})
   */
  public JDBCTemplate(Connection conn,String sql,Map<String,Object> params){
    setConnection(conn);
    setSql(esdk.str.format(sql,sqlDialect.getJDBCTemplateParams(params)));
  }

  public JDBCTemplate setLimit(int limit) {
    this._limit = limit;
    return this;
  }

  public JDBCTemplate setLimit(int limit, int offset) {
    this._limit = limit;
    this._offset = offset;
    return this;
  }

  public JDBCTemplate setOffset(int offset) {
    this._offset = offset;
    return this;
  }

  public JDBCTemplate setSql(String sql) {
    _sql = sql;
    if (!this._noParameters) {
      Object[] newParams = new Object[EasyStr.getSubStringCount(_sql, "?")];
      System.arraycopy(_parameters, 0, newParams, 0, _parameters.length);
      _parameters = newParams;
    }
    return this;
  }

  public JDBCTemplate clearParameters() {
    _parameters = new Object[0];
    return this;
  }


  /**
   * 能正确处理in条件，Object可以为数组、Collection和Literal
   */
  public JDBCTemplate setParameter(int position, Object value) {
    this._parameters[position] = value;
    return this;
  }

  public JDBCTemplate setParameters(Object... values) {
    for (int i = 0; i < values.length; i++) {
      this.setParameter(i, values[i]);
    }
    return this;
  }

  public Integer queryForInt() throws SQLException {
    Object obj = queryForObject();
    return esdk.obj.convert(obj, Integer.class);
  }

  public Long queryForLong() throws SQLException {
    Object obj = queryForObject();
    return esdk.obj.convert(obj, Long.class);
  }

  public Double queryForDouble() throws SQLException {
    Object obj = queryForObject();
    return esdk.obj.convert(obj, Double.class);
  }

  public String queryForString() throws SQLException {
    Object obj = queryForObject();
    return esdk.obj.convert(obj, String.class);
  }

  public Object queryForObject() throws SQLException {
    if ((SQLAssistant.isMySQL() || SQLAssistant.isPostgreSQL()) && !_sql.contains(" limit "))
      _sql = _sql.trim() + " limit 1";
    ABResultSet rs = toABResultSet();
    Object result = null;
    if (rs.next()) {
      String firstFieldName = rs.getColumns()[0];
      result = rs.getRow().get(firstFieldName);
    }
    rs.close();
    return result;
  }

  public Object[][] toArray2(boolean isOutputColumn, String... columns) throws SQLException {
    ResultSet rs = toResultSet();
    Object[][] result = isOutputColumn ? EasySql.resultSetToArrWithHeader(rs, columns) : EasySql.rsToArr2(rs, columns);
    EasySql.close(rs);
    return result;
  }

  public Object[][] toArray2() throws SQLException {
    return toArray2(true);
  }

  public List<List> toList2() throws SQLException {
    return toABResultSet().toList();
  }

  public ABRow getFirstRow() throws SQLException {
    ABResultSet abrs = toABResultSet();
    if (abrs.next()) {
      abrs.close();
      return (ABRow) abrs.getCurrentRow();
    } else
      return null;
  }

  public ABRow getFirstRow(boolean isCreateInstance) throws SQLException {
    ABResultSet abrs = toABResultSet();
    ABRow result = (ABRow) abrs.getFirstRow(isCreateInstance);
    abrs.close();
    return result;
  }

  public List<Object[]> toList(String... columns) throws SQLException {
    return EasySql.toList(toResultSet(), columns);
  }

  public List<Object> toList(String column) throws SQLException {
    return EasySql.toList(toResultSet(), column);
  }

  public ABResultSet<IRow> toABResultSet() throws SQLException{
    ABResultSet<IRow> result=null;
    if(useCacheSec<=0||!isCachable){
      result=new ABResultSet(toResultSet());
      result.setSelect(this);
      return result;
    }
    else{
      String cacheKey=SQLAssistant.getCacheKey(this);
      if(RedisUtils.exists(cacheKey)){
        result=(ABResultSet)RedisUtils.getObj(cacheKey);
      }
      if(result==null){
        result=new ABResultSet(toResultSet());
        RedisUtils.setObj(cacheKey,result,useCacheSec);
      }
      return result;
    }
  }

  public <RS extends ParentResultSet> RS toParentResultSet(Class<RS> resultsetClass) throws Exception {
    parse();
    ParentResultSet parentResultSet = null;
    if (useCacheSec <= 0 || !isCachable) {
      parentResultSet = createParentResultSet(resultsetClass);
    } else {
      RedisClient redis = new RedisClient();
      String cacheKey = SQLAssistant.getCacheKey(this);
      if (redis.exists(cacheKey)) {
        parentResultSet = (ParentResultSet) redis.getObj(cacheKey);
      }
      if (parentResultSet == null) {
        redis.del(redis.keys(cacheKey + "*"));//注意一定要有*号结尾
        parentResultSet = createParentResultSet(resultsetClass);
        parentResultSet.last();
        redis.setObj(cacheKey, parentResultSet, useCacheSec);
      }
      redis.close();
    }
    parentResultSet.setSelect(this);
    return (RS) parentResultSet;
  }

  private <RS extends ParentResultSet> RS createParentResultSet(Class resultsetClass) throws Exception {
    java.lang.reflect.Constructor constructor = resultsetClass.getConstructor(new Class[]{ResultSet.class});
    ParentResultSet parentResultSet = (ParentResultSet) constructor.newInstance(new Object[]{toResultSet()});
    return (RS) parentResultSet;
  }

  public <R extends ParentRow> ABRowSet<R> toRowSet(Class<R> parentRowCls) throws Exception {
    Class resultsetCls = esdk.reflect.loadClass(parentRowCls.getName().replaceAll("Row$", "ResultSet"));
    isShowFullSelectSql=true;
    return (ABRowSet<R>) new ABRowSet(toParentResultSet(resultsetCls));
  }

  public ABRowSet toRowSet() throws SQLException {
    isShowFullSelectSql=true;
    return new ABRowSet(toABResultSet());
  }

  public ResultSet toResultSet() throws SQLException {
    perform();
    return _rs;
  }

  public List toList(IRowMappper mapper) throws SQLException {
    perform();
    List result = new ArrayList();
    for (int i = 0; _rs.next(); i++) {
      result.add(mapper.mapRow(_rs, i));
    }
    EasySql.close(_rs);
    return result;
  }

  public List toList(IRowMappper mapper, int start, int limit) throws SQLException {
    perform();
    List result = new ArrayList();
    limit = limit < 0 ? Integer.MAX_VALUE : limit;
    for (int i = 0, n = start + limit; i < n && _rs.next(); i++) {
      if (i >= start)
        result.add(mapper.mapRow(_rs, i));
    }
    EasySql.close(_rs);
    return result;
  }

  public List toList(final Class pojoClass, int start, int limit) throws SQLException {
    return toList(new ReflectRowMapper(pojoClass), start, limit);
  }

  public List toList(final Class pojoClass) throws SQLException {
    return toList(pojoClass, 0, -1);
  }

  public JSONArray toJsonArray() throws SQLException {
    return toABResultSet().toJsonArray();
  }

  public JSONArray toJsonArray(boolean isFormatJavaBeanName) throws SQLException {
    return toABResultSet().toJsonArray(isFormatJavaBeanName);
  }

  public void clear() {
    _sql = null;
  }

  public Connection getConnection() {
    return _conn;
  }

  public String getSQL() {
    parse();
    return querySQL;
  }

  public void parse() {
    String result = adjustDialect(_sql);
    for (int i = 0; _parameters != null && i < _parameters.length; i++) {
      String param = SQLAssistant.getStmtSqlValue(_parameters[i]);
      if (param.startsWith("(") && param.endsWith(")"))
        param = param.substring(1, param.length() - 1);
      result = result.replaceFirst("\\?", param);
    }
    querySQL=result;
  }

  private String adjustDialect(String sql) {
    String result = sqlDialect.convert(sql,_limit,_offset);
    return result;
  }

  /**
   * 用于解决一次性删除太多数据(如超过100万)所造成锁表的问题，改为每次删除1万，直到全部成功删除为止
   * */
  public int perform(boolean untilAllUpdated) throws SQLException {
    int result=0;
    if(untilAllUpdated){
      int updatedCount=0;
      do{
        updatedCount=perform();
        result+=updatedCount;

      }while(updatedCount>0);
    }
    else
      result=perform();
    return result;
  }

  public int perform() throws SQLException {
    if (_conn == null)
      throw new SQLException("Connection is null,please setConnectcion first");
    if (_conn.isClosed())
      throw new SQLException("Connection is closed");
    adjustLiteralParams();
    String sql = adjustDialect(_sql);
    PreparedStatement pstmt = _conn.prepareStatement(sql);
    for (int i = 0; _parameters != null && i < _parameters.length; i++) {
      if (_parameters[i] != null && _parameters[i].getClass().equals(Date.class))
        _parameters[i] = new Timestamp(((Date) _parameters[i]).getTime());
      pstmt.setObject(i + 1, _parameters[i]);
    }
    TimeMeter tm = TimeMeter.startNew();
    if (esdk.regex.find(sql,"insert +into ")) {
      updatedCount = pstmt.executeUpdate();
      getGeneratedKeys(pstmt);
    } else if (esdk.regex.find(sql,"update |delete +from ")) {
      updatedCount = pstmt.executeUpdate();
    } else if (esdk.regex.find(sql,"select ")) {
      _rs = pstmt.executeQuery();
      updatedCount=0;
    } else {
      boolean result = pstmt.execute();
      updatedCount=0;
      _rs = pstmt.getResultSet();
    }
    this.executeConcumingTime=tm.getElapse();
    if (isShowSql&&!isShowFullSelectSql) {
      SQLAssistant.showSql(this);
    }
    return updatedCount;
  }

  private boolean getGeneratedKeys(PreparedStatement pstmt) throws SQLException {
    if (isAutoIncrement) {
      if (SQLAssistant.isPostgreSQL()) {
        ResultSet rs = pstmt.getResultSet();
        if (rs.next()) {
          generatedKey = rs.getBigDecimal(1);
        }
      } else {
        ResultSet gkrs = pstmt.getGeneratedKeys();
        if (gkrs.next()) {
          generatedKey = (Number) gkrs.getObject(1);
        }
        gkrs.close();
      }
    }
    return generatedKey != null;
  }

  public Number insert() throws SQLException {
    int result = perform();
    if (result>0)
      return generatedKey;
    return null;
  }

  public int update() throws SQLException {
    perform();
    return updatedCount;
  }

  private void adjustLiteralParams() {
    if (_parameters.length == 0) return;
    boolean flag = false;
    for (int i = 0; i < _parameters.length; i++) {
      if (_parameters[i] == null)
        continue;
      else if (_parameters[i] instanceof Literal || _parameters[i] instanceof Collection || _parameters[i].getClass().isArray()) {
        flag = true;
        break;
      }
    }
    if (flag) {
      String _sql1 = _sql;
      int start = 0;
      ArrayList paraList = new ArrayList(_parameters.length);
      for (int i = 0; i < _parameters.length; i++) {
        start = _sql1.indexOf('?', start);
        if (start >= 0) {
          String param = sqlDialect.getJDBCTemplateParams(_parameters[i]);
          _sql1 = _sql1.substring(0, start) + param + _sql1.substring(start + 1);
          start += param.length() + 1;
        }
      }
      _sql = _sql1;
      _parameters = paraList.toArray();
    }
  }

  public int getUpdatedCount() {
    return updatedCount;
  }

  public void setConnection(Connection con) {
    _conn = con;
    sqlDialect=SQLAssistant.setDefaultDialect(con);
  }

  @Override
  public String toString() {
    return getSQL();
  }

  /**
   * 对sql进行count查询，如果出现groupby或union则直接改为子查询方式简化count语句
   */
  @Override
  public int count() throws SQLException {
    String origSql = this._sql;
    String sql=this._sql;
    if (EasyObj.isBlank(sql))
      throw new SQLRuntimeException("sql is null");
    if (!EasyRegex.startWith(sql, "select"))
      throw new SQLRuntimeException("count() is only for select:" + sql);
    int selectIndex = EasyRegex.indexOf(sql, "select");
    int fromIndex = EasyRegex.indexOf(sql, "from");
    if (selectIndex >= fromIndex || selectIndex == -1)
      throw new SQLRuntimeException("sql can not found select and from keywords:" + _sql);
    Object[] origParms = this._parameters;
    int origLimit=this._limit;
    int origOffset=this._offset;
    if (sql.matches("(?i).*?( union | group +by ).*?")) {
      if (sql.matches("(?i).+?LIMIT +\\?( +OFFSET +\\?)?.*?")) {
        sql = this.getSQL();
        this._parameters = new Object[0];
      }
      sql = sql.replaceFirst("(?i)LIMIT [\\d\\?]+ +OFFSET +[\\d\\?]+", "");
      sql = "select count(*) as cnt from (\n" + sql + "\n) a";
    } else{
      sql=sql.substring(0,selectIndex+6)+" count(1) as cnt "+sql.substring(fromIndex,sql.length());
    }
    sql = sql.replaceFirst("(?i) order by +([ ,\\.\\w]+?)([);\n]| group | limit |$)","$2");
    this._sql=sql;
    this._limit=0;
    this._offset=0;
    ABResultSet rs = this.toABResultSet();
    int result = rs.next() ? rs.getRow().getInteger("cnt") : 0;
    rs.close();
    _sql = origSql;
    _parameters = origParms;
    this._limit=origLimit;
    this._offset=origOffset;
    return result;
  }

  public JDBCTemplate useCache(int sec) {
    this.useCacheSec = sec;
    return this;
  }

  public JDBCTemplate useCache(boolean ifCachiable) {
    return useCache(ifCachiable ? Constant.DefaultCacheSec : Constant.ResetUseCacheSec);
  }

  @Override public long getExecuteConsuming(){
    return executeConcumingTime;
  }

}
